package henu.dao.impl;

import henu.bean.Car;
import henu.bean.Company;
import henu.bean.DataBaseSeq;
import henu.bean.Exam;
import henu.dao.CarDao;
import henu.dao.factory.DaoFactory;
import henu.util.Dbcp;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;     
  


public class CarDaoImpl implements CarDao {
	
	
	@Override
	public int Save(Car car) {
		
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		int result = 0;
		
		//如果不需要格式,可直接用dt,dt就是当前系统时间
		Date dt=new Date();	
		//设置显示格式
		DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		
		
		String sql = "INSERT INTO CARINFORMATION VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {car.getCarid(),car.getUserid(),car.getComid(),car.getVin(),car.getEngineno(),car.getVehicleLicense(),car.getVfn(),car.getCarLength(),car.getCarWidth(),car.getCarHeight(),car.getDeadWeight(),car.getHorsePower(),car.getVehicleType(),car.getStatus(),df.format(dt),car.getBuyTime(),car.getQrCode(),car.getCopyLicence(),car.getCarPhoto(),car.getIsTrailer(),car.getInvoice(),car.getMemo1(),car.getMemo2(),car.getMemo3() };
		try {
			result = runner.update(sql, params);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see henu.dao.impl.CarDao#delete(java.lang.String)
	 */
	@Override
	public int delete(int carid) {
			// DELETE FROM Exam WHERE qid=1 ;
			String sql = "DELETE FROM CARINFORMATION WHERE carid=?";
			int result = 0;
			QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
			try {
				result = runner.update(sql, carid);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return result;
		}
	
	/* (non-Javadoc)
	 * @see henu.dao.impl.CarDao#Update(henu.bean.Car)
	 */
	@Override
	public int Update(Car car) {
		// Exam exam = new Exam();
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		int result = 0;
		// INSERT INTO Exam VALUES(1,'最好的编程语言是什么?','java','常识','基本');
		String sql = "UPDATE CARINFORMATION SET userid=?,comid=?,vin=?,engineno=?,vehicleLicense=?,vfn=?,carLength=?,carWidth=?,carHeight=?,deadWeight=?,horsePower=?,vehicleType=?,status=?,buyTime=?,qrCode=?,copyLicence=?,carPhoto=?,isTrailer=?,invoice=? ,memo1=? ,memo2=? ,memo3=?  WHERE carid=?";
		Object[] params = {car.getUserid(),car.getComid(),car.getVin(),car.getEngineno(),car.getVehicleLicense(),car.getVfn(),car.getCarLength(),car.getCarWidth(),car.getCarHeight(),car.getDeadWeight(),car.getHorsePower(),car.getVehicleType(),car.getStatus(),car.getBuyTime(),car.getQrCode(),car.getCopyLicence(),car.getCarPhoto(),car.getIsTrailer(),car.getInvoice(),car.getMemo1(),car.getMemo2(),car.getMemo3(),car.getCarid()};
		try {
			result = runner.update(sql, params);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return result;
	}
	
	/* (non-Javadoc)
	 * @see henu.dao.impl.CarDao#findById(java.lang.String)
	 */
	@Override
	public Car findById(int carid) {
		// 此处返回是bean ,查出来的所有值赋值给bean然后把bean返回
		Car car = null;
		String sql = "select * from CARINFORMATION where carid=?";
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			car = runner.query(sql,	new BeanHandler<Car>(Car.class), carid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return car;
	}
	
	/* (non-Javadoc)
	 * @see henu.dao.impl.CarDao#findByProperty(java.lang.String, java.lang.String, java.lang.String, java.lang.String, int, int)
	 */
	@Override
	public List<Car> findByProperty(String property, String key,
			String order, String sort,int start,int end) {
		// TODO Auto-generated method stub
		List<Car> list=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from CARINFORMATION where "+property+"='"+key+"'  order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			list = qr.query(sql, new BeanListHandler<Car>(Car.class));
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 	
		return list;
	}
	
	public List<Car> findAll(String order, String sort) {
		// TODO Auto-generated method stub
		List<Car> list = new ArrayList<Car>();
		QueryRunner qr = DaoFactory.getRunner();
		String sql = " select * from CARINFORMATION order by "+order+" "+sort+" ";
		try {
			list = qr.query(sql, new BeanListHandler<Car>(Car.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public DataBaseSeq getNowSeq() {
		// TODO Auto-generated method stub
		DataBaseSeq nowNum=null;
		QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
		String sql = "select car_sequence.nextval from CARINFORMATION where rownum <= 1";
		try {
			nowNum=qr.query(sql, new BeanHandler<DataBaseSeq>(DataBaseSeq.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return nowNum;
	}

	@Override
	public List<Car> findAll(String order, String sort, String property,String key) {
		// TODO Auto-generated method stub
		
		List<Car> list = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * FROM Carinformation where " + property + " = "+key
				+ " ORDER BY  " + order + " " + sort;
		try {
			list = runner.query(sql, new BeanListHandler<Car>(
					Car.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}	


}
